﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class TariffDetailDA : ITariffDetailDA
    {
        private string CHARGE_INVOCATION = "@CHARGE_INVOCATION";
        private string CHARGE_PER_UNIT = "@CHARGE_PER_UNIT";
        private string CHARGE_UNIT = "@CHARGE_UNIT";
        private string COST_INVOCATION = "@COST_INVOCATION";
        private string COST_UNIT_RATE = "@COST_UNIT_RATE";
        private string COSTING_UNIT = "@COSTING_UNIT";
        private string CREATED_BY = "@CREATED_BY";
        private string DESCR = "@DESCR";
        private string GENERAL_LEDGER_ID = "@GENERAL_LEDGER_ID";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string LINE_ID = "@LINE_ID";
        private string LOWEST_CHARGE = "@LOWEST_CHARGE";
        private string RATE_ID = "@RATE_ID";
        private string SQL_CHECK_TARIFF_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@TARIFF_DETAIL WHERE TARIFF_ID = @TARIFF_ID AND TARIFF_TYPE = @TARIFF_TYPE ";
        private string SQL_DELETE_TARIFF_DETAIL = " DELETE FROM @_@TARIFF_DETAIL WHERE TARIFF_ID = @TARIFF_ID ";
        private string SQL_INSERT_TARIFF_DETAIL = " INSERT INTO @_@TARIFF_DETAIL ( WH_ID, TARIFF_ID, LINE_ID, TARIFF_TYPE, DESCR, RATE_ID, CHARGE_UNIT, CHARGE_PER_UNIT, CHARGE_INVOCATION, TAX_GROUP_ID, GENERAL_LEDGER_ID, LOWEST_CHARGE, COST_UNIT_RATE, COSTING_UNIT, UNIT_COST, COST_INVOCATION, USER1, USER2, USER3, USER4, USER5, USER6, USER7, USER8, USER9, USER10, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @TARIFF_ID, @LINE_ID, @TARIFF_TYPE, @DESCR, @RATE_ID, @CHARGE_UNIT, @CHARGE_PER_UNIT, @CHARGE_INVOCATION, @TAX_GROUP_ID, @GENERAL_LEDGER_ID, @LOWEST_CHARGE, @COST_UNIT_RATE, @COSTING_UNIT, @UNIT_COST, @COST_INVOCATION, @USER1, @USER2, @USER3, @USER4, @USER5, @USER6, @USER7, @USER8, @USER9, @USER10, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_TARIFF_DETAIL = " SELECT  ROWID, WH_ID, TARIFF_ID, LINE_ID, TARIFF_TYPE, DESCR, RATE_ID, CHARGE_UNIT, CHARGE_PER_UNIT, CHARGE_INVOCATION, TAX_GROUP_ID, GENERAL_LEDGER_ID, LOWEST_CHARGE, COST_UNIT_RATE, COSTING_UNIT, UNIT_COST, COST_INVOCATION, USER1, USER2, USER3, USER4, USER5, USER6, USER7, USER8, USER9, USER10, TS, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@TARIFF_DETAIL ";
        private string SQL_SELECT_ALL_TARIFF_DETAIL_LANGUAGE = " SELECT TD.ROWID, TD.WH_ID, TD.TARIFF_ID, TD.LINE_ID, T.[TEXT] AS TARIFF_TYPE_D,TD.TARIFF_TYPE, TD.DESCR, TD.RATE_ID,  T1.[TEXT] AS CHARGE_UNIT, TD.CHARGE_PER_UNIT, TD.CHARGE_INVOCATION, TD.TAX_GROUP_ID, TD.GENERAL_LEDGER_ID, TD.LOWEST_CHARGE, TD.COST_UNIT_RATE, TD.COSTING_UNIT, TD.UNIT_COST, TD.COST_INVOCATION, TD.USER1, TD.USER2, TD.USER3, TD.USER4, TD.USER5, TD.USER6, TD.USER7, TD.USER8, TD.USER9, TD.USER10, TD.TS, TD.IS_ACTIVE, TD.CREATED_BY, TD.CREATED_DATE, TD.UPDATED_BY, TD.UPDATED_DATE  FROM  @_@TARIFF_DETAIL TD LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = TD.TARIFF_TYPE AND SCD.SYS_CODE_TYPE = 'TARIFFTYPE' LEFT JOIN SYSADM.TRANSLATION T ON 'TARIFFTYPE.'+TD.TARIFF_TYPE = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY ='@LANGUAGE' LEFT JOIN @_@SYS_CODE_DETAIL SCD1 ON SCD1.SYS_CODE = TD.CHARGE_UNIT AND SCD1.SYS_CODE_TYPE = 'SETTLEMENTMODE' LEFT JOIN SYSADM.TRANSLATION T1 ON 'SETTLEMENTMODE.'+TD.CHARGE_UNIT = T1.ITEM_ID  AND  T1.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T1.LANG+'-'+T1.COUNTRY ='@LANGUAGE' ";
        private string SQL_UPDATE_TARIFF_DETAIL = " UPDATE @_@TARIFF_DETAIL SET WH_ID = @WH_ID, LINE_ID = @LINE_ID, DESCR = @DESCR, RATE_ID = @RATE_ID, CHARGE_UNIT = @CHARGE_UNIT, CHARGE_PER_UNIT = @CHARGE_PER_UNIT, CHARGE_INVOCATION = @CHARGE_INVOCATION, TAX_GROUP_ID = @TAX_GROUP_ID, GENERAL_LEDGER_ID = @GENERAL_LEDGER_ID, LOWEST_CHARGE = @LOWEST_CHARGE, COST_UNIT_RATE = @COST_UNIT_RATE, COSTING_UNIT = @COSTING_UNIT, UNIT_COST = @UNIT_COST, COST_INVOCATION = @COST_INVOCATION, USER1 = @USER1, USER2 = @USER2, USER3 = @USER3, USER4 = @USER4, USER5 = @USER5, USER6 = @USER6, USER7 = @USER7, USER8 = @USER8, USER9 = @USER9, USER10 = @USER10, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE TARIFF_ID = @TARIFF_ID AND TARIFF_TYPE = @TARIFF_TYPE  ";
        private string TARIFF_ID = "@TARIFF_ID";
        private string TARIFF_TYPE = "@TARIFF_TYPE";
        private string TAX_GROUP_ID = "@TAX_GROUP_ID";
        private string TS = "@TS";
        private string UNIT_COST = "@UNIT_COST";
        private string UPDATED_BY = "@UPDATED_BY";
        private string USER1 = "@USER1";
        private string USER10 = "@USER10";
        private string USER2 = "@USER2";
        private string USER3 = "@USER3";
        private string USER4 = "@USER4";
        private string USER5 = "@USER5";
        private string USER6 = "@USER6";
        private string USER7 = "@USER7";
        private string USER8 = "@USER8";
        private string USER9 = "@USER9";
        private string WH_ID = "@WH_ID";

        public bool CheckTariffDetailIDUnique(string tariffID, string tariffType, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TARIFF_ID", tariffID), new SqlParameter("@TARIFF_TYPE", tariffType) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_TARIFF_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckTariffDetailIDUnique(DataBase dataBase, DbTransaction tran, string tariffID, string tariffType, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TARIFF_ID", tariffID), new SqlParameter("@TARIFF_TYPE", tariffType) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_TARIFF_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteTariffDetail(string tariffID, List<string> tariffType, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                string strSql = this.SQL_DELETE_TARIFF_DETAIL.Replace("@_@", whLoginID) + "AND TARIFF_TYPE = @TARIFF_TYPE";
                for (int j = 0; j < tariffType.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TARIFF_ID", tariffID), new SqlParameter("@TARIFF_TYPE", tariffType[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteTariffDetail(DataBase dataBase, DbTransaction tran, string tariffID, string whLoginID)
        {
            int result = 0;
            try
            {
                string strSql = this.SQL_DELETE_TARIFF_DETAIL.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TARIFF_ID", tariffID) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql, paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteTariffDetail(DataBase dataBase, DbTransaction tran, string tariffID, List<string> tariffType, string whLoginID)
        {
            int result = 0;
            try
            {
                string strSql = this.SQL_DELETE_TARIFF_DETAIL.Replace("@_@", whLoginID) + "AND TARIFF_TYPE = @TARIFF_TYPE";
                for (int j = 0; j < tariffType.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TARIFF_ID", tariffID), new SqlParameter("@TARIFF_TYPE", tariffType[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllTariffDetail(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_TARIFF_DETAIL.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllTariffDetail(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_TARIFF_DETAIL.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public List<TariffDetailInfo> GetTariffDetailByID(string tariffID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_TARIFF_DETAIL_LANGUAGE.Replace("@_@", whLoginID).Replace("@LANGUAGE", pubParasInfo.UserLanguage) + " WHERE TARIFF_ID = @TARIFF_ID";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TARIFF_ID", tariffID) };
            List<TariffDetailInfo> tariffDetailInfoList = new List<TariffDetailInfo>();
            TariffDetailInfo tariffDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    tariffDetailInfo = new TariffDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TARIFF_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["TARIFF_TYPE"].ToString(), reader["DESCR"].ToString(), reader["RATE_ID"].ToString(), reader["CHARGE_UNIT"].ToString(), reader["CHARGE_PER_UNIT"].ToString(), reader["CHARGE_INVOCATION"].ToString(), reader["TAX_GROUP_ID"].ToString(), reader["GENERAL_LEDGER_ID"].ToString(), reader["LOWEST_CHARGE"].ToString(), (reader["COST_UNIT_RATE"].ToString() != "") ? decimal.Parse(reader["COST_UNIT_RATE"].ToString()) : 0M, reader["COSTING_UNIT"].ToString(), (reader["UNIT_COST"].ToString() != "") ? decimal.Parse(reader["UNIT_COST"].ToString()) : 0M, reader["COST_INVOCATION"].ToString(), reader["USER1"].ToString(), reader["USER2"].ToString(), reader["USER3"].ToString(), reader["USER4"].ToString(), reader["USER5"].ToString(), reader["USER6"].ToString(), reader["USER7"].ToString(), reader["USER8"].ToString(), reader["USER9"].ToString(), reader["USER10"].ToString(), (byte[]) reader["TS"], reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["TARIFF_TYPE_D"].ToString());
                    tariffDetailInfoList.Add(tariffDetailInfo);
                }
            }
            return tariffDetailInfoList;
        }

        public TariffDetailInfo GetTariffDetailByID(string tariffID, string tariffType, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_TARIFF_DETAIL_LANGUAGE.Replace("@_@", whLoginID).Replace("@LANGUAGE", pubParasInfo.UserLanguage) + " WHERE TARIFF_ID = @TARIFF_ID AND TARIFF_TYPE = @TARIFF_TYPE  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TARIFF_ID", tariffID), new SqlParameter("@TARIFF_TYPE", tariffType) };
            TariffDetailInfo tariffDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    tariffDetailInfo = new TariffDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TARIFF_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["TARIFF_TYPE"].ToString(), reader["DESCR"].ToString(), reader["RATE_ID"].ToString(), reader["CHARGE_UNIT"].ToString(), reader["CHARGE_PER_UNIT"].ToString(), reader["CHARGE_INVOCATION"].ToString(), reader["TAX_GROUP_ID"].ToString(), reader["GENERAL_LEDGER_ID"].ToString(), reader["LOWEST_CHARGE"].ToString(), (reader["COST_UNIT_RATE"].ToString() != "") ? decimal.Parse(reader["COST_UNIT_RATE"].ToString()) : 0M, reader["COSTING_UNIT"].ToString(), (reader["UNIT_COST"].ToString() != "") ? decimal.Parse(reader["UNIT_COST"].ToString()) : 0M, reader["COST_INVOCATION"].ToString(), reader["USER1"].ToString(), reader["USER2"].ToString(), reader["USER3"].ToString(), reader["USER4"].ToString(), reader["USER5"].ToString(), reader["USER6"].ToString(), reader["USER7"].ToString(), reader["USER8"].ToString(), reader["USER9"].ToString(), reader["USER10"].ToString(), (byte[]) reader["TS"], reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["TARIFF_TYPE_D"].ToString());
                }
            }
            return tariffDetailInfo;
        }

        public TariffDetailInfo GetTariffDetailByID(DataBase dataBase, DbTransaction tran, string tariffID, string tariffType, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_TARIFF_DETAIL_LANGUAGE.Replace("@_@", whLoginID).Replace("@LANGUAGE", pubParasInfo.UserLanguage) + " WHERE TARIFF_ID = @TARIFF_ID AND TARIFF_TYPE = @TARIFF_TYPE  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TARIFF_ID", tariffID), new SqlParameter("@TARIFF_TYPE", tariffType) };
            TariffDetailInfo tariffDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                tariffDetailInfo = new TariffDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TARIFF_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["TARIFF_TYPE"].ToString(), reader["DESCR"].ToString(), reader["RATE_ID"].ToString(), reader["CHARGE_UNIT"].ToString(), reader["CHARGE_PER_UNIT"].ToString(), reader["CHARGE_INVOCATION"].ToString(), reader["TAX_GROUP_ID"].ToString(), reader["GENERAL_LEDGER_ID"].ToString(), reader["LOWEST_CHARGE"].ToString(), (reader["COST_UNIT_RATE"].ToString() != "") ? decimal.Parse(reader["COST_UNIT_RATE"].ToString()) : 0M, reader["COSTING_UNIT"].ToString(), (reader["UNIT_COST"].ToString() != "") ? decimal.Parse(reader["UNIT_COST"].ToString()) : 0M, reader["COST_INVOCATION"].ToString(), reader["USER1"].ToString(), reader["USER2"].ToString(), reader["USER3"].ToString(), reader["USER4"].ToString(), reader["USER5"].ToString(), reader["USER6"].ToString(), reader["USER7"].ToString(), reader["USER8"].ToString(), reader["USER9"].ToString(), reader["USER10"].ToString(), (byte[]) reader["TS"], reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["TARIFF_TYPE_D"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return tariffDetailInfo;
        }

        public DataSet GetTariffDetailByQueryList(List<string> sqlWhere, TariffDetailQueryEntity tariffdetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_TARIFF_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!tariffdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, tariffdetailQuery.CurrentPage, tariffdetailQuery.PageSize, tariffdetailQuery.SortField, tariffdetailQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetTariffDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, TariffDetailQueryEntity tariffdetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_TARIFF_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, tariffdetailQuery.CurrentPage, tariffdetailQuery.PageSize, tariffdetailQuery.SortField, tariffdetailQuery.SortDirection);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertTariffDetail(TariffDetailInfo tariffDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                tariffDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_TariffDetail_Parameters(tariffDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_TARIFF_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertTariffDetail(DataBase dataBase, DbTransaction tran, TariffDetailInfo tariffDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                tariffDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_TariffDetail_Parameters(tariffDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_TARIFF_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_TariffDetail_Parameters(TariffDetailInfo tariffDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.TARIFF_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.TARIFF_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.DESCR, SqlDbType.VarChar, 20), new SqlParameter(this.RATE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CHARGE_UNIT, SqlDbType.VarChar, 20), new SqlParameter(this.CHARGE_PER_UNIT, SqlDbType.VarChar, 20), new SqlParameter(this.CHARGE_INVOCATION, SqlDbType.VarChar, 20), new SqlParameter(this.TAX_GROUP_ID, SqlDbType.VarChar, 20), new SqlParameter(this.GENERAL_LEDGER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LOWEST_CHARGE, SqlDbType.VarChar, 20), new SqlParameter(this.COST_UNIT_RATE, SqlDbType.Decimal, 13), new SqlParameter(this.COSTING_UNIT, SqlDbType.VarChar, 20), new SqlParameter(this.UNIT_COST, SqlDbType.Decimal, 13), new SqlParameter(this.COST_INVOCATION, SqlDbType.VarChar, 20), 
                new SqlParameter(this.USER1, SqlDbType.VarChar, 30), new SqlParameter(this.USER2, SqlDbType.VarChar, 30), new SqlParameter(this.USER3, SqlDbType.VarChar, 30), new SqlParameter(this.USER4, SqlDbType.VarChar, 30), new SqlParameter(this.USER5, SqlDbType.VarChar, 30), new SqlParameter(this.USER6, SqlDbType.VarChar, 30), new SqlParameter(this.USER7, SqlDbType.VarChar, 30), new SqlParameter(this.USER8, SqlDbType.VarChar, 30), new SqlParameter(this.USER9, SqlDbType.VarChar, 30), new SqlParameter(this.USER10, SqlDbType.VarChar, 30), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(tariffDetailInfo.WhID))
            {
                paramArray[0].Value = tariffDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = tariffDetailInfo.TariffID;
            paramArray[2].Value = tariffDetailInfo.LineID;
            paramArray[3].Value = tariffDetailInfo.TariffType;
            if (!string.IsNullOrEmpty(tariffDetailInfo.Descr))
            {
                paramArray[4].Value = tariffDetailInfo.Descr;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.RateID))
            {
                paramArray[5].Value = tariffDetailInfo.RateID;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.ChargeUnit))
            {
                paramArray[6].Value = tariffDetailInfo.ChargeUnit;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.ChargePerUnit))
            {
                paramArray[7].Value = tariffDetailInfo.ChargePerUnit;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.ChargeInvocation))
            {
                paramArray[8].Value = tariffDetailInfo.ChargeInvocation;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            paramArray[9].Value = tariffDetailInfo.TaxGroupID;
            paramArray[10].Value = tariffDetailInfo.GeneralLedgerID;
            paramArray[11].Value = tariffDetailInfo.LowestCharge;
            paramArray[12].Value = tariffDetailInfo.CostUnitRate;
            if (!string.IsNullOrEmpty(tariffDetailInfo.CostingUnit))
            {
                paramArray[13].Value = tariffDetailInfo.CostingUnit;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            paramArray[14].Value = tariffDetailInfo.UnitCost;
            if (!string.IsNullOrEmpty(tariffDetailInfo.CostInvocation))
            {
                paramArray[15].Value = tariffDetailInfo.CostInvocation;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.User1))
            {
                paramArray[0x10].Value = tariffDetailInfo.User1;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.User2))
            {
                paramArray[0x11].Value = tariffDetailInfo.User2;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.User3))
            {
                paramArray[0x12].Value = tariffDetailInfo.User3;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.User4))
            {
                paramArray[0x13].Value = tariffDetailInfo.User4;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.User5))
            {
                paramArray[20].Value = tariffDetailInfo.User5;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.User6))
            {
                paramArray[0x15].Value = tariffDetailInfo.User6;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.User7))
            {
                paramArray[0x16].Value = tariffDetailInfo.User7;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.User8))
            {
                paramArray[0x17].Value = tariffDetailInfo.User8;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.User9))
            {
                paramArray[0x18].Value = tariffDetailInfo.User9;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(tariffDetailInfo.User10))
            {
                paramArray[0x19].Value = tariffDetailInfo.User10;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            paramArray[0x1a].Value = tariffDetailInfo.IsActive;
            paramArray[0x1b].Value = tariffDetailInfo.CreatedBy;
            paramArray[0x1c].Value = tariffDetailInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateTariffDetail(TariffDetailInfo tariffDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                tariffDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_TariffDetail_Parameters(tariffDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_TARIFF_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateTariffDetail(DataBase dataBase, DbTransaction tran, TariffDetailInfo tariffDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                tariffDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_TariffDetail_Parameters(tariffDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_TARIFF_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

